using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VICC_GBMOV
    {
        public DataRow Convert2ICC_GBMOV(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryICC_GBMOV(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["ICCDAT"] = " ".PadRight(0 ); 
         dwReturn["ICCLO"] = " ".PadRight(2 ); 
         dwReturn["ICCLB"] = " ".PadRight(2 ); 
         dwReturn["ICCTYPE"] = " ".PadRight(3 ); 
         dwReturn["GABMANDANT"] = " ".PadRight(2 ); 
         dwReturn["GABJJJJ"] = " ".PadRight(0 ); 
         dwReturn["GABBELEG"] = " ".PadRight(0 ); 
         dwReturn["GABLFDNR"] = " ".PadRight(0 ); 
         dwReturn["GABPARTIE"] = " ".PadRight(7 ); 
         dwReturn["GABPARTIEKZ"] = " ".PadRight(1 ); 
         dwReturn["GPLIEFSCH"] = " ".PadRight(8 ); 
         dwReturn["GPSPPART"] = " ".PadRight(10 ); 
         dwReturn["GPFBPART"] = " ".PadRight(10 ); 
         dwReturn["GPZWPART"] = " ".PadRight(10 ); 
         dwReturn["GPEINDAT"] = " ".PadRight(0 ); 
         dwReturn["GABCODE"] = " ".PadRight(8 ); 
         dwReturn["GABNAME"] = " ".PadRight(40 ); 
         dwReturn["GPBEM1"] = " ".PadRight(40 ); 
         dwReturn["GPBEM2"] = " ".PadRight(40 ); 
         dwReturn["GABLO"] = " ".PadRight(2 ); 
         dwReturn["GABLB"] = " ".PadRight(2 ); 
         dwReturn["GABLP"] = " ".PadRight(8 ); 
         dwReturn["GABAUFBKZ"] = " ".PadRight(1 ); 
         dwReturn["GABLBEH"] = " ".PadRight(10 ); 
         dwReturn["GABLBEHI"] = " ".PadRight(10 ); 
         dwReturn["GABART"] = " ".PadRight(20 ); 
         dwReturn["GABVAR"] = " ".PadRight(3 ); 
         dwReturn["GABFARBE"] = " ".PadRight(10 ); 
         dwReturn["GABAUFTYP"] = " ".PadRight(1 ); 
         dwReturn["GABAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["GABAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["GABAUFPOS"] = " ".PadRight(3 ); 
         dwReturn["GABAFOLGE"] = " ".PadRight(3 ); 
         dwReturn["GABTEILNR"] = " ".PadRight(4 ); 
         dwReturn["GABSA"] = " ".PadRight(3 ); 
         dwReturn["GABPOS"] = " ".PadRight(2 ); 
         dwReturn["GABKSTKRS"] = " ".PadRight(2 ); 
         dwReturn["GABKST"] = " ".PadRight(4 ); 
         dwReturn["GABBS"] = " ".PadRight(2 ); 
         dwReturn["GABWERTDAT"] = " ".PadRight(0 ); 
         dwReturn["GABKG"] = " ".PadRight(0 ); 
         dwReturn["GABKA"] = " ".PadRight(0 ); 
         dwReturn["GABSP"] = " ".PadRight(0 ); 
         dwReturn["GABWERT"] = " ".PadRight(0 ); 
         dwReturn["GABBUCHDAT"] = " ".PadRight(0 ); 
         dwReturn["GABBUCHZEIT"] = " ".PadRight(0 ); 
         dwReturn["GABBUCHBEN"] = " ".PadRight(8 ); 
         dwReturn["GABFC"] = " ".PadRight(8 ); 
         dwReturn["GABFILLER"] = " ".PadRight(39 ); 
         dwReturn["CHGUSER"] = " ".PadRight(8 ); 
         dwReturn["CHGDATE"] = " ".PadRight(0 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapICC_GBMOV(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_GBMOV(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryICC_GBMOV(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapICC_GBMOV(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertICC_GBMOV(dwLocal, ITXDBVERSION);
            }
            UpdateICC_GBMOV(dwLocal);
        }
        public void InsertICC_GBMOV(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO ICC_GBMOV(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_GBMOV(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM ICC_GBMOV WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = " select * from ( select * from ( SELECT * FROM (SELECT A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,P.GPGART,P.GPGVAR,P.GPGFARBE,P.GPSPPART,P.GPFBPART,P.GPZWPART, "
                        + " (select NVL(TRIM(SUBSTR(S.SDDATEN,2,30)),'') from gsdat x,sddat s where gsart=P.GPGART AND GSVAR = P.GPGVAR  AND SDSA = '0196' "
                        + " AND SDKEY LIKE '%V' AND X.GSMANDANT||X.gsprogrp = SUBSTR(SDKEY,1,5)) AS MATTYP, "
                        + " P.GPEINDAT,P.GPLIEFSCH,SUBSTR(P.GPBEM1,1,15) AS PART,SUBSTR(P.GPBEM1,16) AS ORGIN,TRIM(P.GPBEM2) AS SUPP,A.INKG*0.001 AS INKG,A.INSP, "
                        + " SUM(DECODE(G.ICCTYPE,'POI',NVL(G.GABKG,0)))*0.001 AS POIKG, "
                        + " SUM(DECODE(G.ICCTYPE,'POI',NVL(G.GABSP,0))) AS POISP, "
                        + " SUM(DECODE(G.ICCTYPE,'PDI',NVL(G.GABKG,0)))*0.001 AS PDIKG, "
                        + " SUM(DECODE(G.ICCTYPE,'PDI',NVL(G.GABSP,0))) AS PDISP, "
                        + " SUM(DECODE(G.ICCTYPE,'MUI',NVL(G.GABKG,0)))*0.001 AS MUIKG, "
                        + " SUM(DECODE(G.ICCTYPE,'MUI',NVL(G.GABSP,0))) AS MUISP, "
                        + " SUM(DECODE(G.ICCTYPE,'STI',NVL(G.GABKG,0)))*0.001 AS STIKG, "
                        + " SUM(DECODE(G.ICCTYPE,'STI',NVL(G.GABSP,0))) AS STISP, "
                        + " SUM(DECODE(G.ICCTYPE,'EXI',NVL(G.GABKG,0)))*0.001 AS EXIKG, "
                        + " SUM(DECODE(G.ICCTYPE,'EXI',NVL(G.GABSP,0))) AS EXISP, "
                        + " SUM(DECODE(G.ICCTYPE,'SOI',NVL(G.GABKG,0)))*0.001 AS SOIKG, "
                        + " SUM(DECODE(G.ICCTYPE,'SOI',NVL(G.GABSP,0))) AS SOISP, "
                        + " SUM(DECODE(G.ICCTYPE,'MUO',NVL(G.GABKG,0)))*0.001 AS MUOKG, "
                        + " SUM(DECODE(G.ICCTYPE,'MUO',NVL(G.GABSP,0))) AS MUOSP, "
                        + " SUM(DECODE(G.ICCTYPE,'SMO',NVL(G.GABKG,0)))*0.001 AS SMOKG, "
                        + " SUM(DECODE(G.ICCTYPE,'SMO',NVL(G.GABSP,0))) AS SMOSP, "
                        + " SUM(DECODE(G.ICCTYPE,'STO',NVL(G.GABKG,0)))*0.001 AS STOKG, "
                        + " SUM(DECODE(G.ICCTYPE,'STO',NVL(G.GABSP,0))) AS STOSP, "
                        + " SUM(DECODE(G.ICCTYPE,'EXO',NVL(G.GABKG,0)))*0.001 AS EXOKG, "
                        + " SUM(DECODE(G.ICCTYPE,'EXO',NVL(G.GABSP,0))) AS EXOSP, "
                        + " SUM(DECODE(G.ICCTYPE,'SOO',NVL(G.GABKG,0)))*0.001 AS SOOKG, "
                        + " SUM(DECODE(G.ICCTYPE,'SOO',NVL(G.GABSP,0))) AS SOOSP, "
                        + " (SELECT NVL(SUM(X.GLBSTKG),0)*0.001 FROM ICC_GLLG X WHERE X.ICCDAT = :GABBUCHDAT: AND X.GLMANDANT = A.GLMANDANT  "
                        + " AND X.GLPARTIE = A.GLPARTIE AND X.GLPARTIEKZ = A.GLPARTIEKZ AND X.GLLO = A.GLLO AND X.GLLB = A.GLLB ) AS OUTKG, "
                        + " (SELECT NVL(SUM(X.GLBSTSP),0) FROM ICC_GLLG X WHERE X.ICCDAT = :GABBUCHDAT: AND X.GLMANDANT = A.GLMANDANT "
                        + " AND X.GLPARTIE = A.GLPARTIE AND X.GLPARTIEKZ = A.GLPARTIEKZ AND X.GLLO = A.GLLO AND X.GLLB = A.GLLB ) AS OUTSP, "
                        + " (SELECT STRAGG(TRIM(T2.TXTKTEXT))FROM TXTIND T1 ,TXTKON T2 WHERE T1.TXTIMANDANT =T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                        + " AND TRIM(T1.TXTISIND) =GLPARTIE||GLPARTIEKZ ) AS REMARK "
                        + " FROM ( "
                        + " SELECT A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,SUM(A.GLBSTKG)AS INKG,  "
                        + " SUM(A.GLBSTKA) AS INKA ,SUM(A.GLBSTSP) AS INSP,TRIM(A.GLBEM) AS PACKING "
                        + " FROM ICC_GLLG A WHERE A.ICCDAT = :ICCDAT: AND A.GLLO = :ICCLO: AND A.GLLB = :ICCLB:  "
                        + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,TRIM(A.GLBEM) "
                        + " UNION ALL "
                        + " SELECT A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,0,0,0,TRIM(A.GLBEM) "
                        + " FROM ICC_GLLG A WHERE A.ICCDAT = :GABBUCHDAT: AND A.GLLO = :ICCLO: AND A.GLLB = :ICCLB:  "
                        + " AND NOT EXISTS (SELECT '1' FROM ICC_GLLG B  "
                        + " WHERE B.ICCDAT = :ICCDAT: AND B.GLMANDANT = A.GLMANDANT "
                        + " AND B.GLPARTIE = A.GLPARTIE AND B.GLPARTIEKZ = A.GLPARTIEKZ) "
                        + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,TRIM(A.GLBEM) "
                        + " UNION ALL "
                        + " SELECT DISTINCT A.GABMANDANT,A.GABPARTIE,A.GABPARTIEKZ,A.GABLO,A.GABLB,0,0,0,'' "
                        + " FROM ICC_GBMOV A WHERE A.ICCDAT >= :ICCDAT: AND A.ICCDAT< :GABBUCHDAT: "
                        + " AND A.ICCLO = '01' AND A.ICCLB =:ICCLB:  "
                        + " AND NOT EXISTS  (SELECT '1' FROM ICC_GLLG B  "
                        + " WHERE (B.ICCDAT = :ICCDAT: OR B.ICCDAT =:GABBUCHDAT:) AND B.GLMANDANT = A.GABMANDANT "
                        + " AND B.GLPARTIE = A.GABPARTIE AND B.GLPARTIEKZ = A.GABPARTIEKZ)) A, "
                        + " GPDAT P,(SELECT * from ICC_GBMOV I where I.ICCDAT>=:ICCDAT: AND I.ICCDAT<:GABBUCHDAT: ) G "
                        + " WHERE A.GLMANDANT = P.GPMANDANT(+) AND A.GLPARTIE = P.GPPARTIE(+) "
                        + " AND A.GLPARTIEKZ = P.GPPARTIEKZ(+)  "
                        + " AND A.GLMANDANT = G.GABMANDANT(+) AND A.GLPARTIE = G.GABPARTIE(+) "
                        + " AND A.GLPARTIEKZ = G.GABPARTIEKZ(+) AND A.GLLO = G.ICCLO(+) AND A.GLLB = G.ICCLB(+) "
                        + " GROUP BY A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,P.GPGART,P.GPGVAR,P.GPGFARBE,P.GPSPPART,P.GPFBPART,P.GPZWPART, "
                        + " P.GPEINDAT,P.GPLIEFSCH,P.GPBEM1,P.GPBEM2,A.INKG,A.INSP,A.GLMANDANT) "
                        + " WHERE (GPSPPART =:GPSPPART: OR :GPSPPART: = 'AAAAAAAAAA') AND (GPFBPART LIKE '" + dwData["GPFBPART"] + "%' OR :GPFBPART:='AAAAAA')"
                        + " ORDER BY GPGART,GPGVAR,GPGFARBE,GPSPPART,GPFBPART,GLPARTIE ) "
                        + " union all "
                    + " select '','',gllo,gllb,'ZZZZ','ZZ','','','','',mattyp,0,'','','','',sum(inkg) as inkg,sum(insp) as insp, "
                    + " sum(poikg) as poikg,sum(poisp) as poisp,sum(pdikg) as pdikg,sum(pdisp) as pdisp,sum(muikg) as muikg, "
                    + " sum(muisp) as muisp,sum(stikg) as stikg,sum(stisp) as stisp,sum(exikg) as exikg,sum(exisp) as exisp, "
                    + " sum(soikg) as soikg,sum(soisp) as soisp,sum(muokg) as muokg,sum(muosp) as muosp,sum(smokg) as smokg, "
                    + " sum(smosp) as smosp,sum(stokg) as stokg,sum(stosp) as stosp,sum(exokg) as exokg,sum(exosp) as exosp, "
                    + " sum(sookg) as sookg,sum(soosp) as soosp,sum(outkg) as outkg,sum(outsp) as outsp,'' from ( " 
                    + " SELECT * FROM (SELECT A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,P.GPGART,P.GPGVAR,P.GPGFARBE,P.GPSPPART,P.GPFBPART,P.GPZWPART, "
                    + " (select NVL(TRIM(SUBSTR(S.SDDATEN,2,30)),'') from gsdat x,sddat s where gsart=P.GPGART AND GSVAR = P.GPGVAR  AND SDSA = '0196' "
                    + " AND SDKEY LIKE '%V' AND X.GSMANDANT||X.gsprogrp = SUBSTR(SDKEY,1,5)) AS MATTYP, "
                    + " P.GPEINDAT,P.GPLIEFSCH,SUBSTR(P.GPBEM1,1,15) AS PART,SUBSTR(P.GPBEM1,16) AS ORGIN,TRIM(P.GPBEM2) AS SUPP,A.INKG*0.001 AS INKG,A.INSP, "
                    + " SUM(DECODE(G.ICCTYPE,'POI',NVL(G.GABKG,0)))*0.001 AS POIKG, "
                    + " SUM(DECODE(G.ICCTYPE,'POI',NVL(G.GABSP,0))) AS POISP, "
                    + " SUM(DECODE(G.ICCTYPE,'PDI',NVL(G.GABKG,0)))*0.001 AS PDIKG, "
                    + " SUM(DECODE(G.ICCTYPE,'PDI',NVL(G.GABSP,0))) AS PDISP, "
                    + " SUM(DECODE(G.ICCTYPE,'MUI',NVL(G.GABKG,0)))*0.001 AS MUIKG, "
                    + " SUM(DECODE(G.ICCTYPE,'MUI',NVL(G.GABSP,0))) AS MUISP, "
                    + " SUM(DECODE(G.ICCTYPE,'STI',NVL(G.GABKG,0)))*0.001 AS STIKG, "
                    + " SUM(DECODE(G.ICCTYPE,'STI',NVL(G.GABSP,0))) AS STISP, "
                    + " SUM(DECODE(G.ICCTYPE,'EXI',NVL(G.GABKG,0)))*0.001 AS EXIKG, "
                    + " SUM(DECODE(G.ICCTYPE,'EXI',NVL(G.GABSP,0))) AS EXISP, "
                    + " SUM(DECODE(G.ICCTYPE,'SOI',NVL(G.GABKG,0)))*0.001 AS SOIKG, "
                    + " SUM(DECODE(G.ICCTYPE,'SOI',NVL(G.GABSP,0))) AS SOISP, "
                    + " SUM(DECODE(G.ICCTYPE,'MUO',NVL(G.GABKG,0)))*0.001 AS MUOKG, "
                    + " SUM(DECODE(G.ICCTYPE,'MUO',NVL(G.GABSP,0))) AS MUOSP, "
                    + " SUM(DECODE(G.ICCTYPE,'SMO',NVL(G.GABKG,0)))*0.001 AS SMOKG, "
                    + " SUM(DECODE(G.ICCTYPE,'SMO',NVL(G.GABSP,0))) AS SMOSP, "
                    + " SUM(DECODE(G.ICCTYPE,'STO',NVL(G.GABKG,0)))*0.001 AS STOKG, "
                    + " SUM(DECODE(G.ICCTYPE,'STO',NVL(G.GABSP,0))) AS STOSP, "
                    + " SUM(DECODE(G.ICCTYPE,'EXO',NVL(G.GABKG,0)))*0.001 AS EXOKG, "
                    + " SUM(DECODE(G.ICCTYPE,'EXO',NVL(G.GABSP,0))) AS EXOSP, "
                    + " SUM(DECODE(G.ICCTYPE,'SOO',NVL(G.GABKG,0)))*0.001 AS SOOKG, "
                    + " SUM(DECODE(G.ICCTYPE,'SOO',NVL(G.GABSP,0))) AS SOOSP, "
                    + " (SELECT NVL(SUM(X.GLBSTKG),0)*0.001 FROM ICC_GLLG X WHERE X.ICCDAT = :GABBUCHDAT: AND X.GLMANDANT = A.GLMANDANT  "
                    + " AND X.GLPARTIE = A.GLPARTIE AND X.GLPARTIEKZ = A.GLPARTIEKZ AND X.GLLO = A.GLLO AND X.GLLB = A.GLLB ) AS OUTKG, "
                    + " (SELECT NVL(SUM(X.GLBSTSP),0) FROM ICC_GLLG X WHERE X.ICCDAT = :GABBUCHDAT: AND X.GLMANDANT = A.GLMANDANT "
                    + " AND X.GLPARTIE = A.GLPARTIE AND X.GLPARTIEKZ = A.GLPARTIEKZ AND X.GLLO = A.GLLO AND X.GLLB = A.GLLB ) AS OUTSP, "
                    + " (SELECT STRAGG(TRIM(T2.TXTKTEXT))FROM TXTIND T1 ,TXTKON T2 WHERE T1.TXTIMANDANT =T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND TRIM(T1.TXTISIND) =GLPARTIE||GLPARTIEKZ ) AS REMARK "
                    + " FROM ( "
                    + " SELECT A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,SUM(A.GLBSTKG)AS INKG,  "
                    + " SUM(A.GLBSTKA) AS INKA ,SUM(A.GLBSTSP) AS INSP,TRIM(A.GLBEM) AS PACKING "
                    + " FROM ICC_GLLG A WHERE A.ICCDAT = :ICCDAT: AND A.GLLO = :ICCLO: AND A.GLLB = :ICCLB:  "
                    + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,TRIM(A.GLBEM) "
                    + " UNION ALL "
                    + " SELECT A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,0,0,0,TRIM(A.GLBEM) "
                    + " FROM ICC_GLLG A WHERE A.ICCDAT = :GABBUCHDAT: AND A.GLLO = :ICCLO: AND A.GLLB = :ICCLB:  "
                    + " AND NOT EXISTS (SELECT '1' FROM ICC_GLLG B  "
                    + " WHERE B.ICCDAT = :ICCDAT: AND B.GLMANDANT = A.GLMANDANT "
                    + " AND B.GLPARTIE = A.GLPARTIE AND B.GLPARTIEKZ = A.GLPARTIEKZ) "
                    + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,TRIM(A.GLBEM) "
                    + " UNION ALL "
                    + " SELECT DISTINCT A.GABMANDANT,A.GABPARTIE,A.GABPARTIEKZ,A.GABLO,A.GABLB,0,0,0,'' "
                    + " FROM ICC_GBMOV A WHERE A.ICCDAT >= :ICCDAT: AND A.ICCDAT< :GABBUCHDAT: "
                    + " AND A.ICCLO = '01' AND A.ICCLB =:ICCLB:  "
                    + " AND NOT EXISTS  (SELECT '1' FROM ICC_GLLG B  "
                    + " WHERE (B.ICCDAT = :ICCDAT: OR B.ICCDAT =:GABBUCHDAT:) AND B.GLMANDANT = A.GABMANDANT "
                    + " AND B.GLPARTIE = A.GABPARTIE AND B.GLPARTIEKZ = A.GABPARTIEKZ)) A, "
                    + " GPDAT P,(SELECT * from ICC_GBMOV I where I.ICCDAT>=:ICCDAT: AND I.ICCDAT<:GABBUCHDAT: ) G "
                    + " WHERE A.GLMANDANT = P.GPMANDANT(+) AND A.GLPARTIE = P.GPPARTIE(+) "
                    + " AND A.GLPARTIEKZ = P.GPPARTIEKZ(+)  "
                    + " AND A.GLMANDANT = G.GABMANDANT(+) AND A.GLPARTIE = G.GABPARTIE(+) "
                    + " AND A.GLPARTIEKZ = G.GABPARTIEKZ(+) AND A.GLLO = G.ICCLO(+) AND A.GLLB = G.ICCLB(+) "
                    + " GROUP BY A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,P.GPGART,P.GPGVAR,P.GPGFARBE,P.GPSPPART,P.GPFBPART,P.GPZWPART, "
                    + " P.GPEINDAT,P.GPLIEFSCH,P.GPBEM1,P.GPBEM2,A.INKG,A.INSP,A.GLMANDANT) "
                    + " WHERE (GPSPPART =:GPSPPART: OR :GPSPPART: = 'AAAAAAAAAA') AND (GPFBPART LIKE '" + dwData["GPFBPART"] + "%' OR :GPFBPART:='AAAAAA')"
                    + " )  GROUP BY gllo,gllb,mattyp ) ORDER BY gllo,gllb,mattyp,gpgart,gpgvar ";

                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT A.*,'' AS TYP FROM ICC_GBMOV A "
                        + " WHERE ICCDAT >=:ICCDAT: AND ICCDAT<=:GABBUCHDAT:  AND ICCLO = :ICCLO: AND ICCLB = :ICCLB: AND GABMANDANT = :GABMANDANT: AND GABPARTIE = :GABPARTIE: "
                        + " ORDER BY GABBELEG,GABLFDNR ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM (SELECT A.GLPARTIE,A.GLLO,A.GLLB,P.GPGART,P.GPGFARBE,P.GPSPPART,P.GPFBPART, "
                        + " A.PACKING,P.GPEINDAT,trim(p.GPBEM1) as GPBEM1,TRIM(P.GPBEM2) AS GPBEM2, "
                        + " A.INKG*0.001 AS INKG,A.INKA,  "
                        + " SUM(DECODE(G.ICCTYPE,'MUI',NVL(G.GABKG,0)))*0.001 AS MUIKG,   "
                        + " SUM(DECODE(G.ICCTYPE,'MUI',NVL(G.GABKA,0))) AS MUIKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'STI',NVL(G.GABKG,0)))*0.001 AS STIKG,  "
                        + " SUM(DECODE(G.ICCTYPE,'STI',NVL(G.GABKA,0))) AS STIKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'EXI',NVL(G.GABKG,0)))*0.001 AS EXIKG,  "
                        + " SUM(DECODE(G.ICCTYPE,'EXI',NVL(G.GABKA,0))) AS EXIKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'SOI',NVL(G.GABKG,0)))*0.001 AS SOIKG,   "
                        + " SUM(DECODE(G.ICCTYPE,'SOI',NVL(G.GABKA,0))) AS SOIKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'MUO',NVL(G.GABKG,0)))*0.001 AS MUOKG,  " 
                        + " SUM(DECODE(G.ICCTYPE,'MUO',NVL(G.GABKA,0))) AS MUOKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'SMO',NVL(G.GABKG,0)))*0.001 AS SMOKG,  " 
                        + " SUM(DECODE(G.ICCTYPE,'SMO',NVL(G.GABKA,0))) AS SMOKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'STO',NVL(G.GABKG,0)))*0.001 AS STOKG,  "
                        + " SUM(DECODE(G.ICCTYPE,'STO',NVL(G.GABKA,0))) AS STOKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'EXO',NVL(G.GABKG,0)))*0.001 AS EXOKG,  "
                        + " SUM(DECODE(G.ICCTYPE,'EXO',NVL(G.GABKA,0))) AS EXOKA,   "
                        + " SUM(DECODE(G.ICCTYPE,'SOO',NVL(G.GABKG,0)))*0.001 AS SOOKG,  " 
                        + " SUM(DECODE(G.ICCTYPE,'SOO',NVL(G.GABKA,0))) AS SOOKA,   "
                        + " (SELECT NVL(SUM(X.GLBSTKG),0)*0.001 FROM ICC_GLLG X WHERE X.ICCDAT = :GABBUCHDAT: AND X.GLMANDANT = A.GLMANDANT   " 
                        + " AND X.GLPARTIE = A.GLPARTIE AND X.GLPARTIEKZ = A.GLPARTIEKZ AND X.GLLO = A.GLLO AND X.GLLB = A.GLLB ) AS OUTKG,   "
                        + " (SELECT NVL(SUM(X.GLBSTKA),0) FROM ICC_GLLG X WHERE X.ICCDAT = :GABBUCHDAT: AND X.GLMANDANT = A.GLMANDANT   "
                        + " AND X.GLPARTIE = A.GLPARTIE AND X.GLPARTIEKZ = A.GLPARTIEKZ AND X.GLLO = A.GLLO AND X.GLLB = A.GLLB ) AS OUTKA, "
                        + " (SELECT STRAGG(TRIM(T2.TXTKTEXT))FROM TXTIND T1 ,TXTKON T2 WHERE T1.TXTIMANDANT =T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                        + " AND TRIM(T1.TXTISIND) =GLPARTIE||GLPARTIEKZ ) AS REMARK "
                        + " FROM (   "
                        + " SELECT A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,SUM(A.GLBSTKG)AS INKG,    "
                        + " SUM(A.GLBSTKA) AS INKA ,SUM(A.GLBSTSP) AS INSP,MAX(A.GLBEM) AS PACKING  FROM ICC_GLLG A  "
                        + " WHERE A.ICCDAT = :ICCDAT: AND A.GLLO = :ICCLO: AND A.GLLB = :ICCLB:  "
                        + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB   "
                        + " UNION ALL   "
                        + " SELECT A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,0,0,0,MAX(A.GLBEM) AS PACKING  "
                        + " FROM ICC_GLLG A WHERE A.ICCDAT = :GABBUCHDAT: AND A.GLLO = :ICCLO: AND A.GLLB = :ICCLB:  "
                        + " AND NOT EXISTS (SELECT '1' FROM ICC_GLLG B   WHERE B.ICCDAT = :ICCDAT: AND B.GLMANDANT = A.GLMANDANT   "
                        + " AND B.GLPARTIE = A.GLPARTIE AND B.GLPARTIEKZ = A.GLPARTIEKZ)  "
                        + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB  "
                        + " UNION ALL   "
                        + " SELECT DISTINCT A.GABMANDANT,A.GABPARTIE,A.GABPARTIEKZ,A.GABLO,A.GABLB,0,0,0,   "
                        + " (SELECT NVL(MAX(GPABEM),'') FROM GPACK X WHERE X.GPAMANDANT = A.GABMANDANT AND X.GPAPARTIE =A.GABPARTIE  "
                        + " AND X.GPAPARTIEKZ = A.GABPARTIEKZ AND GPALBEHI = A.GABLBEHI ) AS PACKING "
                        + " FROM ICC_GBMOV A WHERE A.ICCDAT >= :ICCDAT: AND A.ICCDAT< :GABBUCHDAT:   "
                        + " AND A.ICCLO = :ICCLO: AND A.ICCLB = :ICCLB:    "
                        + " AND NOT EXISTS  (SELECT '1' FROM ICC_GLLG B    "
                        + " WHERE (B.ICCDAT = :ICCDAT: OR B.ICCDAT =:GABBUCHDAT:) AND B.GLMANDANT = A.GABMANDANT   "
                        + " AND B.GLPARTIE = A.GABPARTIE AND B.GLPARTIEKZ = A.GABPARTIEKZ)) A,   "
                        + " GPDAT P,(SELECT * from ICC_GBMOV I where I.ICCDAT>=:ICCDAT: AND I.ICCDAT<:GABBUCHDAT: ) G   "
                        + " WHERE A.GLMANDANT = P.GPMANDANT(+) AND A.GLPARTIE = P.GPPARTIE(+)  AND A.GLPARTIEKZ = P.GPPARTIEKZ(+)    "
                        + " AND A.GLMANDANT = G.GABMANDANT(+) AND A.GLPARTIE = G.GABPARTIE(+)  AND A.GLPARTIEKZ = G.GABPARTIEKZ(+)  "
                        + " AND A.GLLO = G.ICCLO(+) AND A.GLLB = G.ICCLB(+)   "
                        + " GROUP BY A.GLMANDANT,A.GLPARTIE,A.GLPARTIEKZ,A.GLLO,A.GLLB,P.GPGART,P.GPGFARBE,P.GPSPPART,P.GPFBPART, "
                        + " A.PACKING,P.GPEINDAT,P.GPBEM1,P.GPBEM2,A.INKG,A.INKA ) "
                        + " WHERE GPSPPART =:GPSPPART: OR :GPSPPART: = 'AAAAAAAAAA' "
                        + " ORDER BY GPGART,GPGFARBE,GPSPPART,GPFBPART,GLPARTIE ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 99:
                    or1.CommandText = "SELECT * FROM ICC_GBMOV  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_GBMOV";
            Return = Result;
            return Return;
        }
        public void DeleteICC_GBMOV(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_GBMOV   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateICC_GBMOV(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE ICC_GBMOV SET "
                    +"ICCDAT= :ICCDAT,"
                    +"ICCLO= :ICCLO,"
                    +"ICCLB= :ICCLB,"
                    +"ICCTYPE= :ICCTYPE,"
                    +"GABMANDANT= :GABMANDANT,"
                    +"GABJJJJ= :GABJJJJ,"
                    +"GABBELEG= :GABBELEG,"
                    +"GABLFDNR= :GABLFDNR,"
                    +"GABPARTIE= :GABPARTIE,"
                    +"GABPARTIEKZ= :GABPARTIEKZ,"
                    +"GPLIEFSCH= :GPLIEFSCH,"
                    +"GPSPPART= :GPSPPART,"
                    +"GPFBPART= :GPFBPART,"
                    +"GPZWPART= :GPZWPART,"
                    +"GPEINDAT= :GPEINDAT,"
                    +"GABCODE= :GABCODE,"
                    +"GABNAME= :GABNAME,"
                    +"GPBEM1= :GPBEM1,"
                    +"GPBEM2= :GPBEM2,"
                    +"GABLO= :GABLO,"
                    +"GABLB= :GABLB,"
                    +"GABLP= :GABLP,"
                    +"GABAUFBKZ= :GABAUFBKZ,"
                    +"GABLBEH= :GABLBEH,"
                    +"GABLBEHI= :GABLBEHI,"
                    +"GABART= :GABART,"
                    +"GABVAR= :GABVAR,"
                    +"GABFARBE= :GABFARBE,"
                    +"GABAUFTYP= :GABAUFTYP,"
                    +"GABAUFKRS= :GABAUFKRS,"
                    +"GABAUFLFD= :GABAUFLFD,"
                    +"GABAUFPOS= :GABAUFPOS,"
                    +"GABAFOLGE= :GABAFOLGE,"
                    +"GABTEILNR= :GABTEILNR,"
                    +"GABSA= :GABSA,"
                    +"GABPOS= :GABPOS,"
                    +"GABKSTKRS= :GABKSTKRS,"
                    +"GABKST= :GABKST,"
                    +"GABBS= :GABBS,"
                    +"GABWERTDAT= :GABWERTDAT,"
                    +"GABKG= :GABKG,"
                    +"GABKA= :GABKA,"
                    +"GABSP= :GABSP,"
                    +"GABWERT= :GABWERT,"
                    +"GABBUCHDAT= :GABBUCHDAT,"
                    +"GABBUCHZEIT= :GABBUCHZEIT,"
                    +"GABBUCHBEN= :GABBUCHBEN,"
                    +"GABFC= :GABFC,"
                    +"GABFILLER= :GABFILLER,"
                    +"CHGUSER= :CHGUSER,"
                    +"CHGDATE= :CHGDATE,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("ICCDAT", OracleType.NVarChar).Value = dwData["ICCDAT"].ToString(); 
          or1.Parameters.Add("ICCLO", OracleType.NVarChar).Value = dwData["ICCLO"].ToString(); 
          or1.Parameters.Add("ICCLB", OracleType.NVarChar).Value = dwData["ICCLB"].ToString(); 
          or1.Parameters.Add("ICCTYPE", OracleType.NVarChar).Value = dwData["ICCTYPE"].ToString(); 
          or1.Parameters.Add("GABMANDANT", OracleType.NVarChar).Value = dwData["GABMANDANT"].ToString(); 
          or1.Parameters.Add("GABJJJJ", OracleType.NVarChar).Value = dwData["GABJJJJ"].ToString(); 
          or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"].ToString(); 
          or1.Parameters.Add("GABLFDNR", OracleType.NVarChar).Value = dwData["GABLFDNR"].ToString(); 
          or1.Parameters.Add("GABPARTIE", OracleType.NVarChar).Value = dwData["GABPARTIE"].ToString(); 
          or1.Parameters.Add("GABPARTIEKZ", OracleType.NVarChar).Value = dwData["GABPARTIEKZ"].ToString(); 
          or1.Parameters.Add("GPLIEFSCH", OracleType.NVarChar).Value = dwData["GPLIEFSCH"].ToString(); 
          or1.Parameters.Add("GPSPPART", OracleType.NVarChar).Value = dwData["GPSPPART"].ToString(); 
          or1.Parameters.Add("GPFBPART", OracleType.NVarChar).Value = dwData["GPFBPART"].ToString(); 
          or1.Parameters.Add("GPZWPART", OracleType.NVarChar).Value = dwData["GPZWPART"].ToString(); 
          or1.Parameters.Add("GPEINDAT", OracleType.NVarChar).Value = dwData["GPEINDAT"].ToString(); 
          or1.Parameters.Add("GABCODE", OracleType.NVarChar).Value = dwData["GABCODE"].ToString(); 
          or1.Parameters.Add("GABNAME", OracleType.NVarChar).Value = dwData["GABNAME"].ToString(); 
          or1.Parameters.Add("GPBEM1", OracleType.NVarChar).Value = dwData["GPBEM1"].ToString(); 
          or1.Parameters.Add("GPBEM2", OracleType.NVarChar).Value = dwData["GPBEM2"].ToString(); 
          or1.Parameters.Add("GABLO", OracleType.NVarChar).Value = dwData["GABLO"].ToString(); 
          or1.Parameters.Add("GABLB", OracleType.NVarChar).Value = dwData["GABLB"].ToString(); 
          or1.Parameters.Add("GABLP", OracleType.NVarChar).Value = dwData["GABLP"].ToString(); 
          or1.Parameters.Add("GABAUFBKZ", OracleType.NVarChar).Value = dwData["GABAUFBKZ"].ToString(); 
          or1.Parameters.Add("GABLBEH", OracleType.NVarChar).Value = dwData["GABLBEH"].ToString(); 
          or1.Parameters.Add("GABLBEHI", OracleType.NVarChar).Value = dwData["GABLBEHI"].ToString(); 
          or1.Parameters.Add("GABART", OracleType.NVarChar).Value = dwData["GABART"].ToString(); 
          or1.Parameters.Add("GABVAR", OracleType.NVarChar).Value = dwData["GABVAR"].ToString(); 
          or1.Parameters.Add("GABFARBE", OracleType.NVarChar).Value = dwData["GABFARBE"].ToString(); 
          or1.Parameters.Add("GABAUFTYP", OracleType.NVarChar).Value = dwData["GABAUFTYP"].ToString(); 
          or1.Parameters.Add("GABAUFKRS", OracleType.NVarChar).Value = dwData["GABAUFKRS"].ToString(); 
          or1.Parameters.Add("GABAUFLFD", OracleType.NVarChar).Value = dwData["GABAUFLFD"].ToString(); 
          or1.Parameters.Add("GABAUFPOS", OracleType.NVarChar).Value = dwData["GABAUFPOS"].ToString(); 
          or1.Parameters.Add("GABAFOLGE", OracleType.NVarChar).Value = dwData["GABAFOLGE"].ToString(); 
          or1.Parameters.Add("GABTEILNR", OracleType.NVarChar).Value = dwData["GABTEILNR"].ToString(); 
          or1.Parameters.Add("GABSA", OracleType.NVarChar).Value = dwData["GABSA"].ToString(); 
          or1.Parameters.Add("GABPOS", OracleType.NVarChar).Value = dwData["GABPOS"].ToString(); 
          or1.Parameters.Add("GABKSTKRS", OracleType.NVarChar).Value = dwData["GABKSTKRS"].ToString(); 
          or1.Parameters.Add("GABKST", OracleType.NVarChar).Value = dwData["GABKST"].ToString(); 
          or1.Parameters.Add("GABBS", OracleType.NVarChar).Value = dwData["GABBS"].ToString(); 
          or1.Parameters.Add("GABWERTDAT", OracleType.NVarChar).Value = dwData["GABWERTDAT"].ToString(); 
          or1.Parameters.Add("GABKG", OracleType.NVarChar).Value = dwData["GABKG"].ToString(); 
          or1.Parameters.Add("GABKA", OracleType.NVarChar).Value = dwData["GABKA"].ToString(); 
          or1.Parameters.Add("GABSP", OracleType.NVarChar).Value = dwData["GABSP"].ToString(); 
          or1.Parameters.Add("GABWERT", OracleType.NVarChar).Value = dwData["GABWERT"].ToString(); 
          or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString(); 
          or1.Parameters.Add("GABBUCHZEIT", OracleType.NVarChar).Value = dwData["GABBUCHZEIT"].ToString(); 
          or1.Parameters.Add("GABBUCHBEN", OracleType.NVarChar).Value = dwData["GABBUCHBEN"].ToString(); 
          or1.Parameters.Add("GABFC", OracleType.NVarChar).Value = dwData["GABFC"].ToString(); 
          or1.Parameters.Add("GABFILLER", OracleType.NVarChar).Value = dwData["GABFILLER"].ToString(); 
          or1.Parameters.Add("CHGUSER", OracleType.NVarChar).Value = dwData["CHGUSER"].ToString(); 
          or1.Parameters.Add("CHGDATE", OracleType.NVarChar).Value = dwData["CHGDATE"].ToString(); 
                    #endregion
                  break;
                case 2:
                  or1.CommandText = "UPDATE ICC_GBMOV A SET ICCDAT = :ICCDAT:,GABKG =:GABKG:,GABKA=:GABKA:,GABSP = :GABSP:,CHGUSER = :CHGUSER:,CHGDATE = :CHGDATE: "
                      + " WHERE GABBELEG= :GABBELEG: AND GABLFDNR=:GABLFDNR: ";
                  or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                  break;
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
